{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "70b86d36",
   "metadata": {},
   "source": [
    "<a href=\"https://colab.research.google.com/github/run-llama/llama_index/blob/main/docs/examples/agent/openai_assistant_query_cookbook.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "b50c4af8-fec3-4396-860a-1322089d76cb",
   "metadata": {},
   "source": [
    "# OpenAI Assistant Advanced Retrieval Cookbook\n",
    "\n",
    "\n",
    "In this notebook, we try out OpenAI Assistant API for advanced retrieval tasks, by plugging in a variety of query engine tools and datasets. The wrapper abstraction we use is our `OpenAIAssistantAgent` class, which allows us to plug in custom tools. We explore how `OpenAIAssistant` can complement/replace existing workflows solved by our retrievers/query engines through its agent execution + function calling loop.\n",
    "\n",
    "- Joint QA + Summarization\n",
    "- Auto retrieval \n",
    "- Joint SQL and vector search"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5887c147-29f1-4a9b-b766-ea6fc01235bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "!pip install llama-index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5befaedd-10f1-4bd7-be46-c235ec237976",
   "metadata": {},
   "outputs": [],
   "source": [
    "import nest_asyncio\n",
    "\n",
    "nest_asyncio.apply()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f428bbe5-d7de-49eb-857c-564a49f948ce",
   "metadata": {},
   "source": [
    "## Joint QA and Summarization\n",
    "\n",
    "In this section we show how we can get the Assistant agent to both answer fact-based questions and summarization questions. This is something that the in-house retrieval tool struggles to accomplish."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "93f80dcf-f07b-43b6-ab4f-4826cfbcff2c",
   "metadata": {},
   "source": [
    "### Load Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de92789e-850a-4668-a459-96318cd74b0a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "--2023-11-11 09:40:13--  https://raw.githubusercontent.com/run-llama/llama_index/main/docs/examples/data/paul_graham/paul_graham_essay.txt\n",
      "Resolving raw.githubusercontent.com (raw.githubusercontent.com)... 2606:50c0:8002::154, 2606:50c0:8003::154, 2606:50c0:8000::154, ...\n",
      "Connecting to raw.githubusercontent.com (raw.githubusercontent.com)|2606:50c0:8002::154|:443... connected.\n",
      "HTTP request sent, awaiting response... 200 OK\n",
      "Length: 75042 (73K) [text/plain]\n",
      "Saving to: ‘data/paul_graham/paul_graham_essay.txt’\n",
      "\n",
      "data/paul_graham/pa 100%[===================>]  73.28K  --.-KB/s    in 0.009s  \n",
      "\n",
      "2023-11-11 09:40:14 (8.24 MB/s) - ‘data/paul_graham/paul_graham_essay.txt’ saved [75042/75042]\n",
      "\n"
     ]
    }
   ],
   "source": [
    "!mkdir -p 'data/paul_graham/'\n",
    "!wget 'https://raw.githubusercontent.com/run-llama/llama_index/main/docs/examples/data/paul_graham/paul_graham_essay.txt' -O 'data/paul_graham/paul_graham_essay.txt'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1eec54ef-b469-46c4-973f-8e213577d063",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index import SimpleDirectoryReader\n",
    "\n",
    "# load documents\n",
    "documents = SimpleDirectoryReader(\"./data/paul_graham/\").load_data()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "227675b4-96ff-45f4-a794-21a831143dfa",
   "metadata": {},
   "source": [
    "### Setup Vector + Summary Indexes/Query Engines/Tools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d4baa45-fbd0-4285-ba92-6e9eeffbd91c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.llms import OpenAI\n",
    "from llama_index import (\n",
    "    ServiceContext,\n",
    "    StorageContext,\n",
    "    SummaryIndex,\n",
    "    VectorStoreIndex,\n",
    ")\n",
    "\n",
    "# initialize service context (set chunk size)\n",
    "llm = OpenAI()\n",
    "service_context = ServiceContext.from_defaults(chunk_size=1024, llm=llm)\n",
    "nodes = service_context.node_parser.get_nodes_from_documents(documents)\n",
    "\n",
    "# initialize storage context (by default it's in-memory)\n",
    "storage_context = StorageContext.from_defaults()\n",
    "storage_context.docstore.add_documents(nodes)\n",
    "\n",
    "# Define Summary Index and Vector Index over Same Data\n",
    "summary_index = SummaryIndex(nodes, storage_context=storage_context)\n",
    "vector_index = VectorStoreIndex(nodes, storage_context=storage_context)\n",
    "\n",
    "# define query engines\n",
    "summary_query_engine = summary_index.as_query_engine(\n",
    "    response_mode=\"tree_summarize\",\n",
    "    use_async=True,\n",
    ")\n",
    "vector_query_engine = vector_index.as_query_engine()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8474aef-4702-46a3-ad0a-da2d94e73e4b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.tools.query_engine import QueryEngineTool\n",
    "\n",
    "summary_tool = QueryEngineTool.from_defaults(\n",
    "    query_engine=summary_query_engine,\n",
    "    name=\"summary_tool\",\n",
    "    description=(\n",
    "        \"Useful for summarization questions related to the author's life\"\n",
    "    ),\n",
    ")\n",
    "\n",
    "vector_tool = QueryEngineTool.from_defaults(\n",
    "    query_engine=vector_query_engine,\n",
    "    name=\"vector_tool\",\n",
    "    description=(\n",
    "        \"Useful for retrieving specific context to answer specific questions about the author's life\"\n",
    "    ),\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "214c21d9-d530-44f0-83aa-6443354a3d70",
   "metadata": {},
   "source": [
    "### Define Assistant Agent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f7553e1-6ca4-43b8-b485-50487f4691ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.agent import OpenAIAssistantAgent\n",
    "\n",
    "agent = OpenAIAssistantAgent.from_new(\n",
    "    name=\"QA bot\",\n",
    "    instructions=\"You are a bot designed to answer questions about the author\",\n",
    "    openai_tools=[],\n",
    "    tools=[summary_tool, vector_tool],\n",
    "    verbose=True,\n",
    "    run_retrieve_sleep_time=1.0,\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7694116-9052-4656-a4ca-ba1d4d95b170",
   "metadata": {},
   "source": [
    "#### Results: A bit flaky"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "afa22f7a-6cb3-4d75-a99d-3c80e3a9fb09",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== Calling Function ===\n",
      "Calling function: summary_tool with args: {\"input\":\"Can you give me a summary about the author's life?\"}\n",
      "Got output: The author, Paul Graham, had a strong interest in writing and programming from a young age. They started writing short stories and experimenting with programming in high school. In college, they initially studied philosophy but switched to studying artificial intelligence. However, they realized that the AI being practiced at the time was not going to lead to true understanding of natural language. This led them to focus on Lisp programming and eventually write a book about Lisp hacking. Despite being in a PhD program in computer science, the author also developed a passion for art and decided to pursue it further. They attended the Accademia di Belli Arti in Florence but found that it did not teach them much. They then returned to the US and got a job at a software company. Afterward, they attended the Rhode Island School of Design but dropped out due to the focus on developing a signature style rather than teaching the fundamentals of art. They then moved to New York City and became interested in the World Wide Web, eventually starting a company called Viaweb. They later founded Y Combinator, an investment firm, and created Hacker News.\n",
      "========================\n",
      "Paul Graham is an author with eclectic interests and a varied career path. He began with interests in writing and programming, engaged in philosophy and artificial intelligence during college, and authored a book on Lisp programming. With an equally strong passion for art, he studied at the Accademia di Belli Arti in Florence and briefly at the Rhode Island School of Design before immersing himself in the tech industry by starting Viaweb and later founding the influential startup accelerator Y Combinator. He also created Hacker News, a social news website focused on computer science and entrepreneurship. Graham's life reflects a blend of technology, entrepreneurship, and the arts.\n"
     ]
    }
   ],
   "source": [
    "response = agent.chat(\"Can you give me a summary about the author's life?\")\n",
    "print(str(response))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f5be4399-dd5c-4111-95be-0889ab0c7f3d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== Calling Function ===\n",
      "Calling function: vector_tool with args: {\"input\":\"After RICS\"}\n",
      "Got output: After RICS, the author moved back to Providence to continue at RISD. However, it became clear that art school, specifically the painting department, did not have the same relationship to art as medical school had to medicine. Painting students were expected to express themselves and develop a distinctive signature style.\n",
      "========================\n",
      "After the author's time at the Royal Institution of Chartered Surveyors (RICS), they moved back to Providence to continue their studies at the Rhode Island School of Design (RISD). There, the author noted a significant difference in the educational approaches between RISD and medical school, specifically in the painting department. At RISD, students were encouraged to express themselves and to develop a unique and distinctive signature style in their artwork.\n"
     ]
    }
   ],
   "source": [
    "response = agent.query(\"What did the author do after RICS?\")\n",
    "print(str(response))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "db402a8b-90d6-4e1d-8df6-347c54624f26",
   "metadata": {},
   "source": [
    "## AutoRetrieval from a Vector Database\n",
    "\n",
    "Our existing \"auto-retrieval\" capabilities (in `VectorIndexAutoRetriever`) allow an LLM to infer the right query parameters for a vector database - including both the query string and metadata filter.\n",
    "\n",
    "Since the Assistant API can call functions + infer function parameters, we explore its capabilities in performing auto-retrieval here."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "50444bd8",
   "metadata": {},
   "source": [
    "If you're opening this Notebook on colab, you will probably need to install LlamaIndex 🦙."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efba05c6-a80e-4992-8a90-fd8fadd53587",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/jerryliu/Programming/gpt_index/.venv/lib/python3.10/site-packages/pinecone/index.py:4: TqdmExperimentalWarning: Using `tqdm.autonotebook.tqdm` in notebook mode. Use `tqdm.tqdm` instead to force console mode (e.g. in jupyter console)\n",
      "  from tqdm.autonotebook import tqdm\n"
     ]
    }
   ],
   "source": [
    "import pinecone\n",
    "import os\n",
    "\n",
    "api_key = os.environ[\"PINECONE_API_KEY\"]\n",
    "pinecone.init(api_key=api_key, environment=\"us-west1-gcp\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e114fd4e-eefd-48d3-b082-1d918c15dfc9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# dimensions are for text-embedding-ada-002\n",
    "try:\n",
    "    pinecone.create_index(\n",
    "        \"quickstart\", dimension=1536, metric=\"euclidean\", pod_type=\"p1\"\n",
    "    )\n",
    "except Exception:\n",
    "    # most likely index already exists\n",
    "    pass"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8b815ed5-d770-4ec4-8e2c-57b5a4c0941c",
   "metadata": {},
   "outputs": [],
   "source": [
    "pinecone_index = pinecone.Index(\"quickstart\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e889542b-8514-4483-97a6-ff5d032fa75f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{}"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Optional: delete data in your pinecone index\n",
    "pinecone_index.delete(deleteAll=True, namespace=\"test\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7764e84-8f0c-4890-9d6a-5d97be6ed1f9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index import VectorStoreIndex, StorageContext\n",
    "from llama_index.vector_stores import PineconeVectorStore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d5b32f02-e09a-4cfa-b7ba-fa937bdcec9b",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.schema import TextNode\n",
    "\n",
    "nodes = [\n",
    "    TextNode(\n",
    "        text=(\n",
    "            \"Michael Jordan is a retired professional basketball player,\"\n",
    "            \" widely regarded as one of the greatest basketball players of all\"\n",
    "            \" time.\"\n",
    "        ),\n",
    "        metadata={\n",
    "            \"category\": \"Sports\",\n",
    "            \"country\": \"United States\",\n",
    "        },\n",
    "    ),\n",
    "    TextNode(\n",
    "        text=(\n",
    "            \"Angelina Jolie is an American actress, filmmaker, and\"\n",
    "            \" humanitarian. She has received numerous awards for her acting\"\n",
    "            \" and is known for her philanthropic work.\"\n",
    "        ),\n",
    "        metadata={\n",
    "            \"category\": \"Entertainment\",\n",
    "            \"country\": \"United States\",\n",
    "        },\n",
    "    ),\n",
    "    TextNode(\n",
    "        text=(\n",
    "            \"Elon Musk is a business magnate, industrial designer, and\"\n",
    "            \" engineer. He is the founder, CEO, and lead designer of SpaceX,\"\n",
    "            \" Tesla, Inc., Neuralink, and The Boring Company.\"\n",
    "        ),\n",
    "        metadata={\n",
    "            \"category\": \"Business\",\n",
    "            \"country\": \"United States\",\n",
    "        },\n",
    "    ),\n",
    "    TextNode(\n",
    "        text=(\n",
    "            \"Rihanna is a Barbadian singer, actress, and businesswoman. She\"\n",
    "            \" has achieved significant success in the music industry and is\"\n",
    "            \" known for her versatile musical style.\"\n",
    "        ),\n",
    "        metadata={\n",
    "            \"category\": \"Music\",\n",
    "            \"country\": \"Barbados\",\n",
    "        },\n",
    "    ),\n",
    "    TextNode(\n",
    "        text=(\n",
    "            \"Cristiano Ronaldo is a Portuguese professional footballer who is\"\n",
    "            \" considered one of the greatest football players of all time. He\"\n",
    "            \" has won numerous awards and set multiple records during his\"\n",
    "            \" career.\"\n",
    "        ),\n",
    "        metadata={\n",
    "            \"category\": \"Sports\",\n",
    "            \"country\": \"Portugal\",\n",
    "        },\n",
    "    ),\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c7974714-552d-4297-9cc3-6497409496e8",
   "metadata": {},
   "outputs": [],
   "source": [
    "vector_store = PineconeVectorStore(\n",
    "    pinecone_index=pinecone_index, namespace=\"test\"\n",
    ")\n",
    "storage_context = StorageContext.from_defaults(vector_store=vector_store)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6f3fff1-974f-4a72-8f09-874e61c54230",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/json": {
       "ascii": false,
       "bar_format": null,
       "colour": null,
       "elapsed": 0.0056912899017333984,
       "initial": 0,
       "n": 0,
       "ncols": null,
       "nrows": 22,
       "postfix": null,
       "prefix": "Upserted vectors",
       "rate": null,
       "total": 5,
       "unit": "it",
       "unit_divisor": 1000,
       "unit_scale": false
      },
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "c9975a4d317443a2bf3b9f1b98b832a2",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "Upserted vectors:   0%|          | 0/5 [00:00<?, ?it/s]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "index = VectorStoreIndex(nodes, storage_context=storage_context)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "0b557703-3587-41c6-a85b-949094344ca8",
   "metadata": {},
   "source": [
    "#### Define Function Tool\n",
    "\n",
    "Here we define the function interface, which is passed to OpenAI to perform auto-retrieval.\n",
    "\n",
    "We were not able to get OpenAI to work with nested pydantic objects or tuples as arguments,\n",
    "so we converted the metadata filter keys and values into lists for the function API to work with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "113b467e-b924-4994-9011-86ab98167545",
   "metadata": {},
   "outputs": [],
   "source": [
    "# define function tool\n",
    "from llama_index.tools import FunctionTool\n",
    "from llama_index.vector_stores.types import (\n",
    "    VectorStoreInfo,\n",
    "    MetadataInfo,\n",
    "    ExactMatchFilter,\n",
    "    MetadataFilters,\n",
    ")\n",
    "from llama_index.retrievers import VectorIndexRetriever\n",
    "from llama_index.query_engine import RetrieverQueryEngine\n",
    "\n",
    "from typing import List, Tuple, Any\n",
    "from pydantic import BaseModel, Field\n",
    "\n",
    "# hardcode top k for now\n",
    "top_k = 3\n",
    "\n",
    "# define vector store info describing schema of vector store\n",
    "vector_store_info = VectorStoreInfo(\n",
    "    content_info=\"brief biography of celebrities\",\n",
    "    metadata_info=[\n",
    "        MetadataInfo(\n",
    "            name=\"category\",\n",
    "            type=\"str\",\n",
    "            description=(\n",
    "                \"Category of the celebrity, one of [Sports, Entertainment,\"\n",
    "                \" Business, Music]\"\n",
    "            ),\n",
    "        ),\n",
    "        MetadataInfo(\n",
    "            name=\"country\",\n",
    "            type=\"str\",\n",
    "            description=(\n",
    "                \"Country of the celebrity, one of [United States, Barbados,\"\n",
    "                \" Portugal]\"\n",
    "            ),\n",
    "        ),\n",
    "    ],\n",
    ")\n",
    "\n",
    "\n",
    "# define pydantic model for auto-retrieval function\n",
    "class AutoRetrieveModel(BaseModel):\n",
    "    query: str = Field(..., description=\"natural language query string\")\n",
    "    filter_key_list: List[str] = Field(\n",
    "        ..., description=\"List of metadata filter field names\"\n",
    "    )\n",
    "    filter_value_list: List[str] = Field(\n",
    "        ...,\n",
    "        description=(\n",
    "            \"List of metadata filter field values (corresponding to names\"\n",
    "            \" specified in filter_key_list)\"\n",
    "        ),\n",
    "    )\n",
    "\n",
    "\n",
    "def auto_retrieve_fn(\n",
    "    query: str, filter_key_list: List[str], filter_value_list: List[str]\n",
    "):\n",
    "    \"\"\"Auto retrieval function.\n",
    "\n",
    "    Performs auto-retrieval from a vector database, and then applies a set of filters.\n",
    "\n",
    "    \"\"\"\n",
    "    query = query or \"Query\"\n",
    "\n",
    "    exact_match_filters = [\n",
    "        ExactMatchFilter(key=k, value=v)\n",
    "        for k, v in zip(filter_key_list, filter_value_list)\n",
    "    ]\n",
    "    retriever = VectorIndexRetriever(\n",
    "        index,\n",
    "        filters=MetadataFilters(filters=exact_match_filters),\n",
    "        top_k=top_k,\n",
    "    )\n",
    "    results = retriever.retrieve(query)\n",
    "    return [r.get_content() for r in results]\n",
    "\n",
    "\n",
    "description = f\"\"\"\\\n",
    "Use this tool to look up biographical information about celebrities.\n",
    "The vector database schema is given below:\n",
    "{vector_store_info.json()}\n",
    "\"\"\"\n",
    "\n",
    "auto_retrieve_tool = FunctionTool.from_defaults(\n",
    "    fn=auto_retrieve_fn,\n",
    "    name=\"celebrity_bios\",\n",
    "    description=description,\n",
    "    fn_schema=AutoRetrieveModel,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "475cc850-dbc1-4073-896d-a3d811fabfd3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Angelina Jolie is an American actress, filmmaker, and humanitarian. She has received numerous awards for her acting and is known for her philanthropic work.',\n",
       " 'Michael Jordan is a retired professional basketball player, widely regarded as one of the greatest basketball players of all time.']"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "auto_retrieve_fn(\n",
    "    \"celebrity from the United States\",\n",
    "    filter_key_list=[\"country\"],\n",
    "    filter_value_list=[\"United States\"],\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "61f39c99-311d-4609-b13b-ca8f4b4631e9",
   "metadata": {},
   "source": [
    "#### Initialize Agent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "49addd6e-c325-4222-b694-c19be2583650",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.agent import OpenAIAssistantAgent\n",
    "\n",
    "agent = OpenAIAssistantAgent.from_new(\n",
    "    name=\"Celebrity bot\",\n",
    "    instructions=\"You are a bot designed to answer questions about celebrities.\",\n",
    "    tools=[auto_retrieve_tool],\n",
    "    verbose=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6c3fc23-3f09-492f-b1d8-2291cd091a0c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== Calling Function ===\n",
      "Calling function: celebrity_bios with args: {\"query\": \"celebrity from United States\", \"filter_key_list\": [\"country\"], \"filter_value_list\": [\"United States\"]}\n",
      "Got output: ['Angelina Jolie is an American actress, filmmaker, and humanitarian. She has received numerous awards for her acting and is known for her philanthropic work.', 'Michael Jordan is a retired professional basketball player, widely regarded as one of the greatest basketball players of all time.']\n",
      "========================\n",
      "=== Calling Function ===\n",
      "Calling function: celebrity_bios with args: {\"query\": \"celebrity from United States\", \"filter_key_list\": [\"country\"], \"filter_value_list\": [\"United States\"]}\n",
      "Got output: ['Angelina Jolie is an American actress, filmmaker, and humanitarian. She has received numerous awards for her acting and is known for her philanthropic work.', 'Michael Jordan is a retired professional basketball player, widely regarded as one of the greatest basketball players of all time.']\n",
      "========================\n",
      "Here is some information about two celebrities from the United States:\n",
      "\n",
      "1. Angelina Jolie - Angelina Jolie is an American actress, filmmaker, and humanitarian. She has received numerous awards for her acting and is known for her philanthropic work. Over the years, Jolie has starred in several critically acclaimed and commercially successful films, and she has also been involved in various humanitarian causes, advocating for refugees and children's education, among other things.\n",
      "\n",
      "2. Michael Jordan - Michael Jordan is a retired professional basketball player, widely regarded as one of the greatest basketball players of all time. During his career, Jordan dominated the NBA with his scoring ability, athleticism, and competitiveness. He won six NBA championships with the Chicago Bulls and earned the NBA Most Valuable Player Award five times. Jordan has also been a successful businessman and the principal owner of the Charlotte Hornets basketball team.\n",
      "\n",
      "Both figures have made significant impacts in their respective fields and continue to be influential even after reaching the peaks of their careers.\n"
     ]
    }
   ],
   "source": [
    "response = agent.chat(\"Tell me about two celebrities from the United States. \")\n",
    "print(str(response))"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "d641f03d-d0cb-4c11-a571-33a9cb7221d5",
   "metadata": {},
   "source": [
    "## Joint Text-to-SQL and Semantic Search\n",
    "\n",
    "This is currenty handled by our `SQLAutoVectorQueryEngine`.\n",
    "\n",
    "Let's try implementing this by giving our `OpenAIAssistantAgent` access to two query tools: SQL and Vector search."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "550dc181-3725-4b96-8528-2669483955ba",
   "metadata": {},
   "source": [
    "#### Load and Index Structured Data\n",
    "\n",
    "We load sample structured datapoints into a SQL db and index it."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bef04b5c-0e7f-4c44-b71d-8cb26406d50c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import (\n",
    "    create_engine,\n",
    "    MetaData,\n",
    "    Table,\n",
    "    Column,\n",
    "    String,\n",
    "    Integer,\n",
    "    select,\n",
    "    column,\n",
    ")\n",
    "from llama_index import SQLDatabase, SQLStructStoreIndex\n",
    "\n",
    "engine = create_engine(\"sqlite:///:memory:\", future=True)\n",
    "metadata_obj = MetaData()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f3739b3-d1c5-4600-985d-7b6e310eb8eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# create city SQL table\n",
    "table_name = \"city_stats\"\n",
    "city_stats_table = Table(\n",
    "    table_name,\n",
    "    metadata_obj,\n",
    "    Column(\"city_name\", String(16), primary_key=True),\n",
    "    Column(\"population\", Integer),\n",
    "    Column(\"country\", String(16), nullable=False),\n",
    ")\n",
    "\n",
    "metadata_obj.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "251a170a-e1cd-4651-8689-a01d9c82002b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dict_keys(['city_stats'])"
      ]
     },
     "execution_count": null,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# print tables\n",
    "metadata_obj.tables.keys()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3ea511f-5a01-493e-a3f7-ceb144823996",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import insert\n",
    "\n",
    "rows = [\n",
    "    {\"city_name\": \"Toronto\", \"population\": 2930000, \"country\": \"Canada\"},\n",
    "    {\"city_name\": \"Tokyo\", \"population\": 13960000, \"country\": \"Japan\"},\n",
    "    {\"city_name\": \"Berlin\", \"population\": 3645000, \"country\": \"Germany\"},\n",
    "]\n",
    "for row in rows:\n",
    "    stmt = insert(city_stats_table).values(**row)\n",
    "    with engine.begin() as connection:\n",
    "        cursor = connection.execute(stmt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26dfe2b8-4831-4603-a88f-ae3e571f9436",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[('Toronto', 2930000, 'Canada'), ('Tokyo', 13960000, 'Japan'), ('Berlin', 3645000, 'Germany')]\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as connection:\n",
    "    cursor = connection.exec_driver_sql(\"SELECT * FROM city_stats\")\n",
    "    print(cursor.fetchall())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff4e85e7-364b-480b-8a7b-a1db956b7011",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_database = SQLDatabase(engine, include_tables=[\"city_stats\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "faadf01c",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.indices.struct_store.sql_query import NLSQLTableQueryEngine"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "299ad520-1ceb-4191-b843-31b3c2ac3cdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "query_engine = NLSQLTableQueryEngine(\n",
    "    sql_database=sql_database,\n",
    "    tables=[\"city_stats\"],\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "a2f1fc66-6627-4a36-9a5b-bbbd1d89dc05",
   "metadata": {},
   "source": [
    "#### Load and Index Unstructured Data\n",
    "\n",
    "We load unstructured data into a vector index backed by Pinecone"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0b7fdd9d-365a-4978-9352-67c2d6e84599",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: wikipedia in /Users/jerryliu/Programming/gpt_index/.venv/lib/python3.10/site-packages (1.4.0)\n",
      "Requirement already satisfied: requests<3.0.0,>=2.0.0 in /Users/jerryliu/Programming/gpt_index/.venv/lib/python3.10/site-packages (from wikipedia) (2.28.2)\n",
      "Requirement already satisfied: beautifulsoup4 in /Users/jerryliu/Programming/gpt_index/.venv/lib/python3.10/site-packages (from wikipedia) (4.12.2)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in /Users/jerryliu/Programming/gpt_index/.venv/lib/python3.10/site-packages (from requests<3.0.0,>=2.0.0->wikipedia) (3.1.0)\n",
      "Requirement already satisfied: idna<4,>=2.5 in /Users/jerryliu/Programming/gpt_index/.venv/lib/python3.10/site-packages (from requests<3.0.0,>=2.0.0->wikipedia) (3.4)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /Users/jerryliu/Programming/gpt_index/.venv/lib/python3.10/site-packages (from requests<3.0.0,>=2.0.0->wikipedia) (2022.12.7)\n",
      "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /Users/jerryliu/Programming/gpt_index/.venv/lib/python3.10/site-packages (from requests<3.0.0,>=2.0.0->wikipedia) (1.26.15)\n",
      "Requirement already satisfied: soupsieve>1.2 in /Users/jerryliu/Programming/gpt_index/.venv/lib/python3.10/site-packages (from beautifulsoup4->wikipedia) (2.4.1)\n",
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip available: \u001b[0m\u001b[31;49m22.3.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m23.1.2\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "# install wikipedia python package\n",
    "!pip install wikipedia"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e38dd18c-e4e6-402d-93b9-8bb46423e944",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index import (\n",
    "    WikipediaReader,\n",
    "    SimpleDirectoryReader,\n",
    "    VectorStoreIndex,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bf7fc15-b699-4414-8968-5ae67d59ab7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "cities = [\"Toronto\", \"Berlin\", \"Tokyo\"]\n",
    "wiki_docs = WikipediaReader().load_data(pages=cities)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38f97c34-0f9d-4030-a6e5-5526a4f015c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.node_parser import SimpleNodeParser\n",
    "from llama_index import ServiceContext\n",
    "from llama_index.storage import StorageContext\n",
    "from llama_index.text_splitter import TokenTextSplitter\n",
    "from llama_index.llms import OpenAI\n",
    "\n",
    "# define node parser and LLM\n",
    "chunk_size = 1024\n",
    "llm = OpenAI(temperature=0, model=\"gpt-4\")\n",
    "service_context = ServiceContext.from_defaults(chunk_size=chunk_size, llm=llm)\n",
    "text_splitter = TokenTextSplitter(chunk_size=chunk_size)\n",
    "node_parser = SimpleNodeParser.from_defaults(text_splitter=text_splitter)\n",
    "\n",
    "# use default in-memory store\n",
    "storage_context = StorageContext.from_defaults()\n",
    "vector_index = VectorStoreIndex([], storage_context=storage_context)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4dc76bc8-6265-4a0e-957c-1f3d6ac0b802",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Insert documents into vector index\n",
    "# Each document has metadata of the city attached\n",
    "for city, wiki_doc in zip(cities, wiki_docs):\n",
    "    nodes = node_parser.get_nodes_from_documents([wiki_doc])\n",
    "    # add metadata to each node\n",
    "    for node in nodes:\n",
    "        node.metadata = {\"title\": city}\n",
    "    vector_index.insert_nodes(nodes)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "c6e73f5a-5c75-4963-8642-dc5060c0a735",
   "metadata": {},
   "source": [
    "#### Define Query Engines / Tools"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21086866-120d-45fe-9233-20b070643e6a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.tools.query_engine import QueryEngineTool"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13a86ca7-1240-4ea2-9f5f-6d93cca00978",
   "metadata": {},
   "outputs": [],
   "source": [
    "sql_tool = QueryEngineTool.from_defaults(\n",
    "    query_engine=query_engine,\n",
    "    name=\"sql_tool\",\n",
    "    description=(\n",
    "        \"Useful for translating a natural language query into a SQL query over\"\n",
    "        \" a table containing: city_stats, containing the population/country of\"\n",
    "        \" each city\"\n",
    "    ),\n",
    ")\n",
    "vector_tool = QueryEngineTool.from_defaults(\n",
    "    query_engine=vector_index.as_query_engine(similarity_top_k=2),\n",
    "    name=\"vector_tool\",\n",
    "    description=(\n",
    "        f\"Useful for answering semantic questions about different cities\"\n",
    "    ),\n",
    ")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "id": "73780d81-150a-46a7-a935-ab5af22d52ce",
   "metadata": {},
   "source": [
    "#### Initialize Agent"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "651570b6-e2d8-45f6-9c0c-bf5871af49eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "from llama_index.agent import OpenAIAssistantAgent\n",
    "\n",
    "agent = OpenAIAssistantAgent.from_new(\n",
    "    name=\"City bot\",\n",
    "    instructions=\"You are a bot designed to answer questions about cities (both unstructured and structured data)\",\n",
    "    tools=[sql_tool, vector_tool],\n",
    "    verbose=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f467f7d3-1cb1-47e9-8b67-a356f8be55f2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== Calling Function ===\n",
      "Calling function: sql_tool with args: {\"input\":\"SELECT name, country FROM city_stats ORDER BY population DESC LIMIT 1\"}\n",
      "Got output: The city with the highest population is Tokyo, Japan.\n",
      "========================\n",
      "=== Calling Function ===\n",
      "Calling function: vector_tool with args: {\"input\":\"What are the arts and culture like in Tokyo, Japan?\"}\n",
      "Got output: Tokyo has a vibrant arts and culture scene. The city is home to many museums, including the Tokyo National Museum, which specializes in traditional Japanese art, the National Museum of Western Art, and the Edo-Tokyo Museum. There are also theaters for traditional forms of Japanese drama, such as the National Noh Theatre and the Kabuki-za. Tokyo hosts modern Japanese and international pop and rock music concerts, and the New National Theater Tokyo is a hub for opera, ballet, contemporary dance, and drama. The city also celebrates various festivals throughout the year, including the Sannō, Sanja, and Kanda Festivals. Additionally, Tokyo is known for its youth style, fashion, and cosplay in the Harajuku neighborhood.\n",
      "========================\n",
      "Tokyo, Japan, which has the highest population of any city, boasts a rich and diverse arts and culture landscape. The city is a hub for traditional Japanese art as showcased in prominent institutions like the Tokyo National Museum, and it also features artwork from different parts of the world at the National Museum of Western Art. Tokyo has a deep appreciation for its historical roots, with the Edo-Tokyo Museum presenting the past in a detailed and engaging manner.\n",
      "\n",
      "The traditional performing arts have a significant presence in Tokyo, with theaters such as the National Noh Theatre presenting classical Noh dramas and the iconic Kabuki-za offering enchanting Kabuki performances. For enthusiasts of modern entertainment, Tokyo is a prime spot for contemporary music, including both Japanese pop and rock as well as international acts.\n",
      "\n",
      "Opera, ballet, contemporary dance, and drama find a prestigious platform at the New National Theater Tokyo. Tokyo's calendar is filled with a variety of festivals that reflect the city's vibrant cultural heritage, including the Sannō, Sanja, and Kanda Festivals. Additionally, Tokyo is at the forefront of fashion and youth culture, particularly in the Harajuku district, which is famous for its unique fashion, style, and cosplay.\n",
      "\n",
      "This mix of traditional and modern, local and international arts and culture makes Tokyo a dynamic and culturally rich city.\n"
     ]
    }
   ],
   "source": [
    "response = agent.chat(\n",
    "    \"Tell me about the arts and culture of the city with the highest\"\n",
    "    \" population\"\n",
    ")\n",
    "print(str(response))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9ffc35ba-3162-4fa9-81a9-174aa0f1dad3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== Calling Function ===\n",
      "Calling function: vector_tool with args: {\"input\":\"What is the history of Berlin, Germany?\"}\n",
      "Got output: Berlin has a rich and diverse history. It was first documented in the 13th century and has served as the capital of various entities throughout history, including the Margraviate of Brandenburg, the Kingdom of Prussia, the German Empire, the Weimar Republic, and Nazi Germany. After World War II, the city was divided, with West Berlin becoming a part of West Germany and East Berlin becoming the capital of East Germany. Following German reunification in 1990, Berlin once again became the capital of all of Germany. Throughout its history, Berlin has been a center of scientific, artistic, and philosophical activity, and has experienced periods of economic growth and cultural flourishing. Today, it is a world city of culture, politics, media, and science, known for its vibrant arts scene, diverse architecture, and high quality of life.\n",
      "========================\n",
      "Berlin, the capital city of Germany, has a rich and complex history that stretches back to its first documentation in the 13th century. Throughout the centuries, Berlin has been at the heart of numerous important historical movements and events.\n",
      "\n",
      "Initially a small town, Berlin grew in significance as the capital of the Margraviate of Brandenburg. Later on, it ascended in prominence as the capital of the Kingdom of Prussia. With the unification of Germany, Berlin became the imperial capital of the German Empire, a position it retained until the end of World War I.\n",
      "\n",
      "The interwar period saw Berlin as the capital of the Weimar Republic, and it was during this time that the city became known for its vibrant cultural scene. However, the rise of the Nazi regime in the 1930s led to a dark period in Berlin's history, and the city was heavily damaged during World War II.\n",
      "\n",
      "Following the war's end, Berlin became a divided city. The division was physical, represented by the Berlin Wall, and ideological, with West Berlin aligning with democratic West Germany while East Berlin became the capital of the socialist East Germany.\n",
      "\n",
      "The fall of the Berlin Wall in November 1989 was a historic moment, leading to German reunification in 1990. Berlin was once again chosen as the capital of a united Germany. Since reunification, Berlin has undergone massive reconstruction and has become a hub of contemporary culture, politics, media, and science.\n",
      "\n",
      "Today, Berlin celebrates its diverse heritage, from its grand historical landmarks like the Brandenburg Gate and the Reichstag, to its remembrance of the past with monuments such as the Berlin Wall Memorial and the Holocaust Memorial. It is a city known for its cultural dynamism, thriving arts and music scenes, and a high quality of life. Berlin's history has shaped it into a unique world city that continues to play a significant role on the global stage.\n"
     ]
    }
   ],
   "source": [
    "response = agent.chat(\"Tell me about the history of Berlin\")\n",
    "print(str(response))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0873463d-4790-4c17-bfe9-2ece610fe4b3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=== Calling Function ===\n",
      "Calling function: sql_tool with args: {\"input\":\"SELECT name, country FROM city_stats\"}\n",
      "Got output: The cities in the city_stats table are Toronto from Canada, Tokyo from Japan, and Berlin from Germany.\n",
      "========================\n",
      "Here are the countries corresponding to each city:\n",
      "\n",
      "- Toronto: Canada\n",
      "- Tokyo: Japan\n",
      "- Berlin: Germany\n"
     ]
    }
   ],
   "source": [
    "response = agent.chat(\n",
    "    \"Can you give me the country corresponding to each city?\"\n",
    ")\n",
    "print(str(response))"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "llama_index_v2",
   "language": "python",
   "name": "llama_index_v2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
